package com.csproj.dispatch.repository;

import com.csproj.dispatch.domain.Dispatch;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Repository;

import org.springframework.data.jpa.repository.*;

import java.time.ZonedDateTime;
import java.util.Date;
import java.util.List;
import java.util.function.Predicate;


/**
 * Spring Data JPA repository for the Dispatch entity.
 */
@SuppressWarnings("unused")
@Repository
public interface DispatchRepository extends JpaRepository<Dispatch, String>, JpaSpecificationExecutor<Dispatch> {

    public Page<Dispatch> findAll(Specification specification, Pageable pageable);

    public Dispatch findFirstByContainerCodeAndContainerStatusAndDeletionTimeIsNullOrderByCreationTime(String containerCode,
                                                                                                       String containerStatus);
    public Dispatch findFirstByLockCodeAndContainerStatusAndDeletionTimeIsNullOrderByCreationTime(String lockCode, String containerStatus);

    public Dispatch findFirstByLockCodeAndDeletionTimeIsNullAndArriveTimeIsNullOrderByCreationTimeDesc(String lockCode);

    public Dispatch findFirstByContainerCodeAndArriveTimeIsNullAndDeletionTimeIsNullOrderByCreationTime(String containerCode);

    @Query("from Dispatch dp where dp.deletionTime is " +
            "null and (dp.containerStatus = '1' or containerStatus = '3') order by dp.creationTime desc")
    public List<Dispatch> findAllOfUnBind();

    @Query("from Dispatch dp where dp.arriveTime is null and dp.deletionTime is " +
            "null and dp.containerStatus = '3' and dp.lockCode=?1 order by dp.creationTime desc")
    public List<Dispatch> findAllOfBind(String lockCode);

    /*************统计接口***************/
    public List countByRouteCode(Specification predicates);

    @Query(value = "from Dispatch dp where dp.arriveTime is not null and dp.deletionTime is null " +
            "and dp.routeId in (select pt.id from Path pt where pt.startWarehouseId = ?1 and pt.endWarehouseId = ?2)")
    public List<Dispatch> findAllByPath(
            String startWarehouseId, String endWarehouseId);

    @Query(value = "from Dispatch dp where dp.arriveTime is not null and dp.deletionTime is null " +
            "and dp.routeId in (select pt.id from Path pt where pt.startSiteId = ?1 and pt.endSiteId = ?2)")
    public List<Dispatch> findAllByPathAndSite(
            String startSiteId, String endSiteId);

    @Query(value = "from Dispatch dp where dp.arriveTime is not null and dp.deletionTime is null " +
            "and dp.routeId in (select pt.id from Path pt where pt.startSiteId = ?1 and pt.endSiteId = ?2 and endWarehouseId=?3 and startWarehouseId is null)")
    public List<Dispatch> findAllByPathAndSiteNoStartCompany(
            String startSiteId, String endSiteId, String endCompanyId);

    @Query(value = "from Dispatch dp where dp.arriveTime is not null and dp.deletionTime is null " +
            "and dp.routeId in (select pt.id from Path pt where pt.startSiteId = ?1 and pt.endSiteId = ?2 and startWarehouseId=?3 and endWarehouseId is null)")
    public List<Dispatch> findAllByPathAndSiteNoEndCompany(
            String startSiteId, String endSiteId, String startCompanyId);

    public List<Dispatch> findAllByRouteIdAndContainerStatusAndArriveTimeIsNotNullAndLaunchTimeIsNotNullAndLaunchTimeLessThanAndArriveTimeGreaterThan(
            String routeId, String containerStatus, ZonedDateTime launchTime, ZonedDateTime arriveTime);


}
